﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Threading;
using Copad.BusinessCard.Model;
using System.Linq.Expressions;
using System.Reflection;

namespace Copad.BusinessCard.DAL
{
    public class DataBase
    {
        string connectionString = null;
        static object locker = new object();
        static object registerLocker = new object();
        static DataBase singletonDataBase = null;
        static DbProviderFactory fact = DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["ProviderString"]);
        static Dictionary<Type, ModelInfo> typeDict = new Dictionary<Type, ModelInfo>();

        DbTransaction transaction = null;
        bool needTransaction = false;

        static DataBase()
        {
            List<Type> types = new List<Type>();
            types.Add(typeof(Copad.BusinessCard.Model.CardAddress));
            types.Add(typeof(Copad.BusinessCard.Model.CardGroupPermissions));
            types.Add(typeof(Copad.BusinessCard.Model.CardInfo));
            types.Add(typeof(Copad.BusinessCard.Model.CardPhoneNumber));
            types.Add(typeof(Copad.BusinessCard.Model.CardRelation));
            types.Add(typeof(Copad.BusinessCard.Model.CardRemark));
            types.Add(typeof(Copad.BusinessCard.Model.CardUserPermissions));
            types.Add(typeof(Copad.BusinessCard.Model.CardCategory));
            types.Add(typeof(Copad.BusinessCard.Model.CardCompany));
            types.Add(typeof(Copad.BusinessCard.Model.CardEmail));
            types.Add(typeof(Copad.BusinessCard.Model.CardIM));
            types.Add(typeof(Copad.BusinessCard.Model.CardWebsite));
            types.Add(typeof(Copad.BusinessCard.Model.Category));
            types.Add(typeof(Copad.BusinessCard.Model.CategoryGroupPermissions));
            types.Add(typeof(Copad.BusinessCard.Model.CategoryUserPermissions));
            types.Add(typeof(Copad.BusinessCard.Model.Favorite));
            types.Add(typeof(Copad.BusinessCard.Model.Group));
            types.Add(typeof(Copad.BusinessCard.Model.GroupInfo));
            types.Add(typeof(Copad.BusinessCard.Model.Log));
            types.Add(typeof(Copad.BusinessCard.Model.Models));
            types.Add(typeof(Copad.BusinessCard.Model.User));
            types.Add(typeof(Copad.BusinessCard.Model.UserGroup));
            Copad.BusinessCard.DAL.DataBase.Register(types);
        }

        public DataBase()
        {
            this.connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString.Replace("{AppDir}", AppDomain.CurrentDomain.BaseDirectory);
        }

        public DataBase(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public static DataBase GetInstance()
        {

            if (singletonDataBase == null)
            {
                Monitor.Enter(locker);
                if (singletonDataBase == null)
                {
                    singletonDataBase = new DataBase();
                }
                Monitor.Exit(locker);
            }
            return singletonDataBase;
        }

        public static void Register(StandardModel model)
        {
            Register(model.GetType());
        }

        public static void Register(Type type)
        {
            if (typeDict.ContainsKey(type))
            {
                return;
            }
            if (type.BaseType != typeof(StandardModel))
            {
                throw new Exception("不能注册类型" + type.FullName + ",类型必须是" + (typeof(StandardModel).FullName + "的直接子类！"));
            }

            lock (registerLocker)
            {
                if (!typeDict.ContainsKey(type))
                {
                    StandardModel model = (StandardModel)type.Assembly.CreateInstance(type.FullName);
                    var properties = type.GetProperties();
                    ModelInfo info = new ModelInfo();
                    info.ModelType = type;
                    info.TableName = model.TableName;
                    properties.ToList().ForEach(x =>
                    {
                        var attr = x.GetCustomAttributes(typeof(MapIgnoreAttribute), false);
                        if (attr.Length == 0 && x.Name != "TableName")
                        {
                            info.PropertyInfoList.Add(x);
                        }
                    });
                    typeDict.Add(type, info);
                }
            }
        }

        public static void Register(IList<Type> types)
        {
            foreach (Type type in types)
            {
                Register(type);
            }
        }

        public void BeginTransaction()
        {
            needTransaction = true;
            //var connection = fact.CreateConnection();
            //connection.ConnectionString = connectionString;
            //connection.Open();
            //transaction = connection.BeginTransaction();
        }

        public void EndTransaction()
        {
            if (transaction != null)
            {
                transaction.Commit();
            }
            needTransaction = false;
        }

        public void Insert(StandardModel model)
        {
            Type type = model.GetType();
            ModelInfo info = typeDict[type];
            var command = GetCommand(info.InsertSQL);
            info.PropertyInfoList.ForEach(x =>
            {
                if (x.Name == "ID") { return; }
                command.Parameters.Add(CreateParameter("@" + x.Name, x.GetValue(model, null)));
            });
            try
            {
                model.ID = Convert.ToInt64(command.ExecuteScalar());
            }
            catch
            {
                throw;
            }
            finally
            {
                Close(command);
            }
        }

        public void Delete(StandardModel model)
        {
            Type type = model.GetType();
            ModelInfo info = typeDict[type];
            var command = GetCommand(info.DeleteByIDSQL);
            command.Parameters.Add(CreateParameter("@ID", model.ID));
            try
            {
                command.ExecuteNonQuery();
            }
            finally
            {
                Close(command);
            }
        }

        public void Update(StandardModel model)
        {
            Type type = model.GetType();
            ModelInfo info = typeDict[type];
            var command = GetCommand(info.UpdateSQL);
            info.PropertyInfoList.ForEach(x =>
            {
                command.Parameters.Add(CreateParameter("@" + x.Name, x.GetValue(model, null)));
            });
            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                Close(command);
            }
        }

        public List<T> Select<T>(Expression<Func<T, bool>> predicate = null)
        {
            List<T> list = new List<T>();

            Type type = typeof(T);
            if (!typeDict.ContainsKey(type))
            {
                return list;
            }
            ModelInfo info = typeDict[type];
            string sqlCondition = "";
            if (predicate != null)
            {
                //sqlCondition = " where " + predicate.Body.ToString().Replace("AndAlso", "and").Replace("OrElse", "or").Replace("==", "=").Replace("\"", "'").Replace(predicate.Parameters[0].Name, info.TableName);
                sqlCondition = " where " + ExpressionRouter(predicate.Body);
            }

            string sql = info.SelectSQL + sqlCondition;
            var command = GetCommand(sql);


            try
            {
                DbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T model = (T)type.Assembly.CreateInstance(type.FullName);
                    info.PropertyInfoList.ForEach(x =>
                    {
                        if (!reader.IsDBNull(reader.GetOrdinal(x.Name)))
                        {
                            x.SetValue(model, reader[x.Name], null);
                        }
                    });

                    list.Add(model);
                }
            }
            finally
            {
                Close(command);
            }
            return list;
        }

        public List<T> Select<T>(string sql)
        {
            Type type = typeof(T);
            ModelInfo info = typeDict[type];
            var command = GetCommand(sql);
            DbDataReader reader = command.ExecuteReader();
            List<T> list = new List<T>();
            while (reader.Read())
            {
                T model = (T)type.Assembly.CreateInstance(type.FullName);
                info.PropertyInfoList.ForEach(x =>
                {
                    if (!reader.IsDBNull(reader.GetOrdinal(x.Name)))
                    {
                        x.SetValue(model, reader[x.Name], null);
                    }
                });

                list.Add(model);
            }
            Close(command, reader);
            return list;
        }

        public long Count<T>(Expression<Func<T, bool>> predicate = null)
        {
            Type type = typeof(T);
            ModelInfo info = typeDict[type];
            string sqlCondition = "";
            if (predicate != null)
            {
                //sqlCondition = " where " + predicate.Body.ToString().Replace("AndAlso", "and").Replace("OrElse", "or").Replace("==", "=").Replace("\"", "'").Replace(predicate.Parameters[0].Name, info.TableName);
                sqlCondition = " where " + ExpressionRouter(predicate.Body);
            }

            string sql = info.SelectCountSQL + sqlCondition;
            var command = GetCommand(sql);
            try
            {
                return Convert.ToInt64(command.ExecuteScalar());
            }
            finally
            {
                Close(command);
            }
        }

        public void Delete<T>(Expression<Func<T, bool>> predicate = null)
        {
            Type type = typeof(T);
            ModelInfo info = typeDict[type];
            string sqlCondition = "";
            if (predicate != null)
            {
                sqlCondition = " where " + ExpressionRouter(predicate.Body);
            }

            string sql = info.DeleteSQL + sqlCondition;
            var command = GetCommand(sql);
            try
            {
                command.ExecuteNonQuery();
            }
            finally
            {
                Close(command);
            }
        }

        static string BinarExpressionProvider(Expression left, Expression right, ExpressionType type)
        {
            string sb = "(";
            //先处理左边
            sb += ExpressionRouter(left);
            sb += ExpressionTypeCast(type);
            //再处理右边
            string tmpStr = ExpressionRouter(right);
            if (tmpStr == "null")
            {
                if (sb.EndsWith(" ="))
                    sb = sb.Substring(0, sb.Length - 2) + " is null";
                else if (sb.EndsWith("<>"))
                    sb = sb.Substring(0, sb.Length - 2) + " is not null";
            }
            else
                sb += tmpStr;
            return sb += ")";
        }
        //表达式路由计算 
        static string ExpressionRouter(Expression exp)
        {
            string sb = string.Empty;
            if (exp is BinaryExpression)
            {
                BinaryExpression be = ((BinaryExpression)exp);
                return BinarExpressionProvider(be.Left, be.Right, be.NodeType);
            }
            else if (exp is MemberExpression)
            {
                //PropertyExpression,FieldExpression
                MemberExpression me = ((MemberExpression)exp);
                //if (me.Type == typeof(bool))
                //{
                //    return me.Member.Name + "==" + ExpressionRouter(me.Expression);
                //}
                if (me.Expression is ConstantExpression)
                {
                    ConstantExpression ce = (ConstantExpression)me.Expression;
                    object value = ((FieldInfo)me.Member).GetValue(ce.Value);
                    if (value == null)
                    {
                        return "null";
                    }
                    else if (value is ValueType)
                    {
                        return value.ToString();
                    }
                    else if (value is string || value is DateTime || value is char)
                    {
                        return string.Format("'{0}'", value);
                    }
                    else if (value is IEnumerable<object>)
                    {
                        IEnumerable<object> enumerable = (IEnumerable<object>)value;
                        return "'" + string.Join("','", enumerable.ToList()) + "'";
                    }
                    else if (value is List<long>)
                    {
                        List<long> list = (List<long>)value;
                        return string.Join(",", list);
                    }
                    else if (value is Array)
                    {
                        Array array = (Array)value;
                        List<object> list = new List<object>();
                        for (int i = 0; i < array.Length; i++)
                        {
                            list.Add(array.GetValue(i));
                        }
                        if (value is string[] || value is char[])
                        {
                            return "'" + string.Join("','", list) + "'";
                        }
                        else
                        {
                            return string.Join(",", list);
                        }
                    }
                }
                else if (me.Expression.NodeType == ExpressionType.Parameter)
                {
                    return me.Member.Name;
                }
                else if (me.Expression.NodeType == ExpressionType.MemberAccess)
                {
                    object value = Expression.Lambda(me.Expression).Compile().DynamicInvoke(null);
                    switch (me.Member.MemberType)
                    {
                        case MemberTypes.Property:
                            value = me.Member.ReflectedType.GetProperty(me.Member.Name).GetValue(value, null);
                            break;
                        case MemberTypes.Field:
                            value = me.Member.ReflectedType.GetField(me.Member.Name).GetValue(value);
                            break;
                        case MemberTypes.Method:
                            break;
                    }

                    if (value == null)
                    {
                        return "null";
                    }
                    else if (value is Enum)
                    {
                        return Convert.ToInt32(value).ToString();
                    }
                    else if (value is ValueType)
                    {
                        return value.ToString();
                    }
                    else if (value is string || value is DateTime || value is char)
                    {
                        return string.Format("'{0}'", value);
                    }
                }
            }
            else if (exp is NewArrayExpression)
            {
                NewArrayExpression ae = ((NewArrayExpression)exp);
                StringBuilder tmpstr = new StringBuilder();
                foreach (Expression ex in ae.Expressions)
                {
                    tmpstr.Append(ExpressionRouter(ex));
                    tmpstr.Append(",");
                }
                return tmpstr.ToString(0, tmpstr.Length - 1);
            }
            else if (exp is MethodCallExpression)
            {
                MethodCallExpression mce = (MethodCallExpression)exp;
                if (mce.Method.Name == "Like")
                    return string.Format("({0} like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "NotLike")
                    return string.Format("({0} Not like {1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "In")
                    return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[0]), ExpressionRouter(mce.Arguments[1]));
                else if (mce.Method.Name == "NotIn")
                    return string.Format("{0} Not In ({1})", ExpressionRouter(mce.Arguments[1]), ExpressionRouter(mce.Arguments[0]));
                else if (mce.Method.Name == "Contains")
                {
                    string ids = ExpressionRouter(mce.Arguments[0]);
                    if (string.IsNullOrEmpty(ids))
                    {
                        return "1=2"; ;
                    }
                    else if (mce.Arguments.Count == 2)
                    {
                        return string.Format("{0} In ({1})", ExpressionRouter(mce.Arguments[1]), ids);
                    }
                    else
                    {
                        return string.Format("{0} In ({1})", ids, ExpressionRouter(mce.Object));
                    }
                }
            }
            else if (exp is ConstantExpression)
            {
                ConstantExpression ce = ((ConstantExpression)exp);
                if (ce.Value == null)
                    return "null";
                else if (ce.Value is ValueType)
                    if (ce.Value is bool)
                    {
                        return (bool)ce.Value ? "1" : "0";
                    }
                    else
                    {
                        return ce.Value.ToString();
                    }
                else if (ce.Value is string || ce.Value is DateTime || ce.Value is char)
                    return string.Format("'{0}'", ce.Value.ToString());
            }
            else if (exp is UnaryExpression)
            {
                UnaryExpression ue = ((UnaryExpression)exp);
                if (ue.Operand is MethodCallExpression && ue.NodeType == ExpressionType.Not)
                {
                    MethodCallExpression mc = (MethodCallExpression)ue.Operand;
                    if (mc.Method.Name == "Contains")
                    {
                        return string.Format("{0} Not In ({1})", ExpressionRouter(mc.Arguments[1]), ExpressionRouter(mc.Arguments[0]));
                    }
                }
                return ExpressionRouter(ue.Operand);
            }
            else if (exp is ParameterExpression)
            {

            }
            return null;
        }

        static string ExpressionTypeCast(ExpressionType type)
        {
            switch (type)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    return " AND ";
                case ExpressionType.Equal:
                    return " =";
                case ExpressionType.GreaterThan:
                    return " >";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    return " Or ";
                case ExpressionType.Add:
                case ExpressionType.AddChecked:
                    return "+";
                case ExpressionType.Subtract:
                case ExpressionType.SubtractChecked:
                    return "-";
                case ExpressionType.Divide:
                    return "/";
                case ExpressionType.Multiply:
                case ExpressionType.MultiplyChecked:
                    return "*";
                default:
                    return null;
            }
        }

        #region protected method
        protected DbConnection GetConnection()
        {
            DbConnection connection = null;
            if (needTransaction && transaction != null)
            {
                connection = transaction.Connection;
            }
            else
            {
                connection = fact.CreateConnection();
                connection.ConnectionString = connectionString;
            }
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            return connection;
        }

        protected DbCommand GetCommand(string commandText)
        {
            DbCommand command = fact.CreateCommand();
            command.Connection = GetConnection();
            if (needTransaction)
            {
                command.Transaction = transaction;
            }
            command.CommandText = commandText;
            return command;
        }

        protected DbCommand GetCommand()
        {
            return GetCommand("");
        }

        protected void Close(DbConnection connection, DbDataReader reader = null)
        {
            if (connection != null && connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
        }

        protected void Close(DbCommand command, DbDataReader reader = null)
        {
            Close(command.Connection, reader);
        }

        protected DbParameter CreateParameter(string name, object value)
        {
            DbParameter parameter = fact.CreateParameter();
            parameter.ParameterName = name;
            if (value == null)
            {
                parameter.Value = DBNull.Value;
            }
            else
            {
                parameter.Value = value;
            }
            return parameter;
        }
        #endregion
    }
}
